{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cbc9caf8-be1a-43f5-aafe-252ce2cfa32d",
   "metadata": {},
   "source": [
    "![Banner](images/banner.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db9894be-9081-41ef-b77c-4099f6a8bfa4",
   "metadata": {},
   "source": [
    "# Data Frames\n",
    "\n",
    "Documentation reference link: [Working with Data Frames](https://python-oracledb.readthedocs.io/en/latest/user_guide/dataframes.html)\n",
    "\n",
    "<hr>\n",
    "\n",
    "Setup for this notebook:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "41df9da5-373d-407d-8fe7-7d07652e671e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import oracledb\n",
    "\n",
    "un = os.environ.get(\"PYO_SAMPLES_MAIN_USER\", \"pythondemo\")\n",
    "pw = os.environ.get(\"PYO_SAMPLES_MAIN_PASSWORD\", \"welcome\")\n",
    "cs = os.environ.get(\"PYO_SAMPLES_CONNECT_STRING\", \"localhost/orclpdb\")\n",
    "\n",
    "connection = oracledb.connect(user=un, password=pw, dsn=cs)\n",
    "\n",
    "cursor = connection.cursor()\n",
    "try:\n",
    "    cursor.execute(\"drop table mytab\")\n",
    "except:\n",
    "    pass\n",
    "cursor.execute(\"create table mytab (id number, data varchar2(1000))\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ecc6448-868e-4ae8-8637-f89d369764fc",
   "metadata": {},
   "source": [
    "## Working with Data Frames\n",
    "\n",
    "Python-oracledb can query directly to a data frame format, and can also insert data frames into Oracle Database. This can improve performance and reduce memory requirements when your application uses Python data frame libraries such as Apache PyArrow, Pandas, Polars, NumPy, Dask, PyTorch, or writes files in Apache Parquet format.\n",
    "\n",
    "Python-oracledb has two methods for querying into a DataFrame:\n",
    "- `Connection.fetch_df_all()` fetches all rows from a query\n",
    "- `Connection.fetch_df_batches()` implements an iterator for fetching batches of rows"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4308706b-8e7c-4b7f-8fe1-6593e8d11e3c",
   "metadata": {},
   "source": [
    "To fetch all table rows into a Pandas DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "518f59f6-6582-488b-b426-d16aa661c53a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyarrow\n",
    "\n",
    "sql = \"select id, name from SampleQueryTab where id < :idbv order by id\"\n",
    "odf = connection.fetch_df_all(statement=sql, arraysize=100, parameters=[5])\n",
    "\n",
    "# Get a Pandas DataFrame from the data\n",
    "df = pyarrow.table(odf).to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3b12661-bdbe-4c45-a051-ed7cc840780b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "572964be-01d3-4cfc-9fd3-ec316e2256ad",
   "metadata": {},
   "source": [
    "For larger tables you can adjust `arraysize` to optimize network performance."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7dd9bf73-21dc-4fdd-8ede-db4c912875de",
   "metadata": {},
   "source": [
    "You can alter the types and names by specifying a custom schema:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4aabdac-3246-40bd-bff3-c40f7244cff5",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"select id, name from SampleQueryTab where id < :idbv order by id\"\n",
    "\n",
    "schema = pyarrow.schema(\n",
    "    [(\"COL_1\", pyarrow.int16()), \n",
    "     (\"COL_2\", pyarrow.string())]\n",
    ")\n",
    "\n",
    "odf = connection.fetch_df_all(statement=sql, arraysize=100, parameters=[5], requested_schema=schema)\n",
    "df = pyarrow.table(odf).to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "609585ce-3cbc-46bf-9322-36c4b3512c99",
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8982d8b1-ae47-477e-ab3d-088f48ef73e2",
   "metadata": {},
   "source": [
    "## Inserting DataFrames\n",
    "\n",
    "DataFrames from popular libraries can be inserted directly into Oracle Database using `executemany()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b0b23b2e-8811-4f27-bd0b-923fdeb588be",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "\n",
    "# Create a DataFrame manually\n",
    "d = {'A': [1.2, 2.4, 8.9], 'B': [\"Alex\", \"Bobbie\", \"Charlie\"]}\n",
    "pdf = pandas.DataFrame(data=d)\n",
    "\n",
    "# Insert into the database\n",
    "cursor.executemany(\"insert into mytab (id, data) values (:1, :2)\", pdf)\n",
    "\n",
    "# Verify rows\n",
    "rows = cursor.execute(\"select * from mytab\")\n",
    "for r in rows:\n",
    "    print(r)\n",
    "\n",
    "connection.rollback()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0231d747-2f25-4bf3-991e-87035c3c6365",
   "metadata": {},
   "source": [
    "You can also insert large DataFrames using **Direct Path Loading**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1414110c-2923-4561-9dc4-01492e4450fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "SCHEMA_NAME = \"pythondemo\"\n",
    "TABLE_NAME = \"mytab\"\n",
    "COLUMN_NAMES = [\"id\", \"data\"]\n",
    "DATA = pdf\n",
    "\n",
    "connection.direct_path_load(\n",
    "    schema_name=SCHEMA_NAME,\n",
    "    table_name=TABLE_NAME,\n",
    "    column_names=COLUMN_NAMES,\n",
    "    data=DATA\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5fe0e958-a626-4853-9717-9250d6b7e000",
   "metadata": {},
   "source": [
    "Verify inserted data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ec755f98-8f51-459b-b08a-e37a1522d778",
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in cursor.execute('select * from mytab'):\n",
    "    print(row)\n",
    "\n",
    "# Remove the data so the sample can be re-run cleanly\n",
    "cursor.execute(\"truncate table mytab\")\n",
    "connection.commit()    "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.24"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
